package Dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import entity.Longnum;
import entity.Record;
import entity.Setting;
import entity.Str;
import entity.Top;
import entity.User;
import service.SetService;
import service.SetServiceimpl;
import service.TopService;
import service.TopServiceimpl;
import util.Dbconnect;

public class TopDaoimpl implements TopDao{
	SetService ss = new SetServiceimpl();
	 /**
	 * Title: showTop
	 * Description:Top排行榜
	 * @author 毛立新
	 */
    public List<Top> showTop() throws Exception{
		String sql = "select * from top";
		PreparedStatement st = Dbconnect.init(sql);
		List<Top> query = Dbconnect.query(st,Top.class);
		return query;
    }
    /**
     * Title: showByemail
     * Description:按照email查询背诵单词个数
     * @author 毛立新
     */
    public Long showByemail(String email) throws Exception{
    	String sql = "select count(*) num from record where email=?";
    	PreparedStatement st = Dbconnect.init(sql);
        st.setString(1, email);
        Longnum num = Dbconnect.queryone(st, Longnum.class);
        Dbconnect.close(st);
    	return num.getNum();
    }
    /**
     * Title: showBybooktype
     * Description:查询所背诵数据单词总数
     * @author 毛立新
     */
    public Long showBybooktype(String email) throws Exception{
    	String type = ss.getset(email).getBooktype();
    	String sql = "select count(*) num from "+type;
    	PreparedStatement st = Dbconnect.init(sql);
    	Longnum num = Dbconnect.queryone(st, Longnum.class);
        Dbconnect.close(st);
    	return num.getNum();
    }
    /**
     * Title: finish
     * Description:完成度的计算
     * @author 毛立新
     */
	@Override
    public Double getFinish(String email) throws Exception{
		Long type = new TopDaoimpl().showBybooktype(email);
		Long e =  new  TopDaoimpl().showByemail(email);
		Long t = new TopDaoimpl().showBybooktype(email);
    	Double y;
    	y =  ((double)e/t);
    	return y;
    }
	/**
	 * Title: updateTop
	 * Description:清空Top表
	 * @author 毛立新
	 * @return
	 */
   public void ClearTop(String email) throws Exception{
	   String sql = "DELETE from top where email=?;" ;
	   PreparedStatement st = Dbconnect.init(sql);
	   st.setString(1,email);
	   int update = Dbconnect.update(st);
	   Dbconnect.close(st);
	 }
   /**
    * Title: updateTop
      * Description:更新Top记录
      * @author 毛立新
      * @param booktype
      * @return
      * @throws Exception
    */
   public int updateTop(String email) throws Exception{
	   new TopDaoimpl().ClearTop(email);
	   String booktype = ss.getset(email).getBooktype(); 
	   String sql = "insert into top(email,finish) select email, count(*)/ (select count(*) from "
	   		+ booktype+ ")  finish  from record  where email =?  and flag != 0 GROUP  BY email;";
	   PreparedStatement st = Dbconnect.init(sql);
	   st.setString(1, email);
	   int update = Dbconnect.update(st);
	   Dbconnect.close(st);
	   return update;
	}
   
   /**
	 * Title: Getuseremail
	   * Description:从record获得用户email
	   * @author 毛立新
	   * @return
	   * @throws Exception
	 */
	public List<String> Getuseremail()throws Exception{
		String sql = "select DISTINCT email Str from record";
		PreparedStatement st = Dbconnect.init(sql);
		List<Str> query = Dbconnect.query(st,Str.class);
		List<String> ls = new ArrayList<String>();
		for(Str sts:query) {
			ls.add(sts.getStr());
		}
		return ls;
	}/** 
	* Title: newToplist
	* Description:排行榜的生成
	* @author 毛立新
    */
    @Override
    public List<Top> newToplist() throws Exception {
	    // TODO Auto-generated method stub
	    new TopServiceimpl().GetTop();
	    String sql = "select * from top order by finish desc";
	    PreparedStatement st = Dbconnect.init(sql);
	    List<Top> query = Dbconnect.query(st,Top.class);
	    return query;
    }
	
  
}
